import xlrd
import xlsxwriter
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication

# 1)读取
data = xlrd.open_workbook('../myFile/info.xlsx')
classInfo = []
for sheet in data.sheets():
    dit = {'name': sheet.name, 'avgSalary': 0}  # 班级信息,每个sheet页是一个班级
    sum = 0
    for i in range(sheet.nrows):
        if i > 1:
            sum += float(sheet.cell(i, 5).value)
    dit['avgSalary'] = sum / (sheet.nrows - 2)
    classInfo.append(dit)
print('各个班级的平均薪资: ', classInfo)

# 2) 写入excel
workbook = xlsxwriter.Workbook('../myFile/newInfo.xlsx')
sheet = workbook.add_worksheet()  # 创建工作表
# 写入班级数据
nameInfo = []
salaryInfo = []
for item in classInfo:
    nameInfo.append(item['name'])
    salaryInfo.append(item['avgSalary'])
sheet.write_column('A1', nameInfo)  # 按列写入数据
sheet.write_column('B1', salaryInfo)

# 写入图表
chart = workbook.add_chart({'type': 'column'})
# 标题
chart.set_title({'name': '平均就业薪资'})
# 数据源
chart.add_series({
    'name': '班级',
    'categories': '=Sheet1!$A$1:$A$3',
    'values': '=Sheet1!$B$1:$B$3'
})
sheet.insert_chart('A7', chart)
workbook.close()

# 3) 发送邮件
host_server = 'smtp.qq.com'  # 主机地址
sender = "xx@qq.com"  # 需要更新为对应的邮箱
code = "ojoydejqxisdbeeh"  # 需要登录QQ邮箱-->设置-->POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服务-->开启服务
user1 = "xx@qq.com"  # 收件人
# 准备邮件数据
mail_title = "仙女下凡啦"
mail_content = "天降大任于斯人也;梁琴下凡了"

# 构建附件
attachment = MIMEApplication(open('../myFile/newInfo.xlsx', 'rb').read())
attachment.add_header('Content-Disposition', 'attachment', filename='data.xlsx')

smtp = smtplib.SMTP(host_server)
smtp.login(sender, code)  # 登录
msg = MIMEMultipart()  # 带附件的实例
msg['Subject'] = mail_title
msg['From'] = sender
msg['To'] = user1
msg.attach(MIMEText(mail_content))
msg.attach(attachment)

smtp.sendmail(sender, user1, msg.as_string())  # 发送
